package com.navi.loaddata.loadService.impl.PanelData;

import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.gpEntity.CaldDayShift;
import com.navi.loaddata.core.gpEntity.MinMaxEvttimeStamp;
import com.navi.loaddata.core.gpEntity.ShiftStartEndTime;
import com.navi.loaddata.core.gpEntity.YearMonth;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.mysqlEntity.EtlConfDDefect;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class LoadPanelDataByShiftBean {
    private static final Logger logger = LoggerFactory.getLogger(LoadPanelDataByShiftBean.class);

    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    @Autowired
    private MySqlRepositoryImpl dao;


    @Transactional(transactionManager = "secondaryPlatformTransactionManager",
            rollbackFor = {RuntimeException.class, Exception.class})
    public void deleteReworkData(EtlConfDModel etlConfDModel, String tmpTab, List<CaldDayShift> caldDayShiftList, String errMsg, int errCnt) throws Exception {
        Session session = gpDao.getEntityManagerSession();

        StringBuffer sqlbuffDel = new StringBuffer();

        String sqlsumDef = "";
        for (CaldDayShift caldDayShift : caldDayShiftList) {
            sqlsumDef = sqlsumDef + "('" + caldDayShift.getCald_date() + "'," + caldDayShift.getShf_key_fk() + "),";
        }

        sqlsumDef = sqlsumDef.substring(0, sqlsumDef.length() - 1);

        //By ope_id、panel_id、jge_cnt删除前一次rework所在班别的数据
        sqlbuffDel.append("delete from eda." + tmpTab);
        sqlbuffDel.append(" where (cald_date, shf_key) in ");
        sqlbuffDel.append(" (" + sqlsumDef + ")");
//        sqlbuffDel.append(" and ope_id = '" + etlConfDModel.getOpe() + "'");


        try {
            SQLQuery DeltDef_query = session.createSQLQuery(sqlbuffDel.toString());
            logger.info("Delete data SQL:" + sqlbuffDel.toString());

            Date startDate = new Date();

            DeltDef_query.executeUpdate();

            Date end = new Date();
            long durableTime = (end.getTime() - startDate.getTime())/1000;
            logger.info("Delete data耗时:" + durableTime +"s");
        } catch (ETLException2 e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlbuffDel.toString():  **********    " + sqlbuffDel.toString());

            if (!org.apache.commons.lang.StringUtils.isEmpty(errMsg)) {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            } else {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            }

            errCnt++;

            logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
            e.printStackTrace();

            throw new RuntimeException(e);
        }
    }


    @Transactional(transactionManager = "secondaryPlatformTransactionManager",
            rollbackFor = {RuntimeException.class, Exception.class})
    public void deleteData(EtlConfDModel etlConfDModel, String tmpTab, String EvtStartTime, String EvtEndTime, String errMsg, int errCnt) throws Exception {
        Session session = gpDao.getEntityManagerSession();

        StringBuffer sqlbuffDel = new StringBuffer();

        //By ope_id、panel_id、jge_cnt删除前一次rework所在班别的数据
        sqlbuffDel.append("delete from eda." + tmpTab);
        sqlbuffDel.append(" where evt_timestamp >= '" + EvtStartTime + "'");
        sqlbuffDel.append(" and evt_timestamp < '" + EvtEndTime + "'");
//        sqlbuffDel.append(" and ope_id = '" + etlConfDModel.getOpe() + "'");


        try {
            SQLQuery DeltDef_query = session.createSQLQuery(sqlbuffDel.toString());
            logger.info("Delete data SQL:" + sqlbuffDel.toString());

            Date startDate = new Date();

            DeltDef_query.executeUpdate();

            Date end = new Date();
            long durableTime = (end.getTime() - startDate.getTime())/1000;
            logger.info("Delete data耗时:" + durableTime +"s");
        } catch (ETLException2 e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlbuffDel.toString():  **********    " + sqlbuffDel.toString());

            if (!org.apache.commons.lang.StringUtils.isEmpty(errMsg)) {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            } else {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            }

            errCnt++;

            logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
            e.printStackTrace();

            throw new RuntimeException(e);
        }
    }

    @Transactional(transactionManager = "secondaryPlatformTransactionManager",
            rollbackFor = {RuntimeException.class, Exception.class})
    public void insertData(EtlConfDModel etlConfDModel, String tmpTab, String EvtStartTime, String EvtEndTime, String errMsg, int okCnt, int errCnt) throws Exception {
        Session session = gpDao.getEntityManagerSession();

        StringBuffer sqlBuffDef = new StringBuffer();
        StringBuffer sqlInsrDef = new StringBuffer();

        if ("OC".equals(etlConfDModel.getShop())) {
            //获取源数据汇总panel_qty
            sqlBuffDef.append(" select ope_id, evt_timestamp, db_timestamp, ope_mode, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, eqpt_id, prod_id, owner, operator_id, glass_rej_code, cel_repair_grade,");
            sqlBuffDef.append(" cel_repair_flg, glass_grade, glass_defect_cnt, source_ope_id, pnl_rej_type, pnl_judgment_grade, pnl_jge,");
            sqlBuffDef.append(" pnl_grade, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5,");
            sqlBuffDef.append(" panel_qty from (");
            sqlBuffDef.append(" select defectHis.ope_id, timestamp'"+ EvtStartTime +"' as evt_timestamp,");
            sqlBuffDef.append(" now() as db_timestamp, defectHis.cald_date, coalesce(defectHis.ope_mode,'-') as ope_mode, defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.eqpt_id, defectHis.prod_id, coalesce(defectHis.owner,'-') as owner,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.cald_date, defectHis.shf_key_fk, defectHis.ope_id, defectHis.hour_key_fk, defectHis.prod_id, ");
            sqlBuffDef.append(" defectHis.eqpt_id, defectHis.owner, defectHis.operator_id, defectHis.ope_mode, defectHis.glass_rej_code, defectHis.cel_repair_grade, defectHis.cel_repair_flg, ");
            sqlBuffDef.append(" defectHis.glass_grade, sum(defectHis.glass_defect_cnt), defectHis.source_ope_id) as rowCnt,");
            sqlBuffDef.append(" coalesce(defectHis.operator_id,'-') as operator_id, coalesce(defectHis.glass_rej_code,'-') as glass_rej_code, coalesce(defectHis.cel_repair_grade,'-') as cel_repair_grade,");
            sqlBuffDef.append(" coalesce(defectHis.cel_repair_flg,'-') as cel_repair_flg, coalesce(defectHis.glass_grade,'-') as glass_grade, coalesce(sum(defectHis.glass_defect_cnt),0) as glass_defect_cnt,");
            sqlBuffDef.append(" coalesce(defectHis.source_ope_id,'-') as source_ope_id, '-' as pnl_rej_type, '-' as pnl_judgment_grade, '-'as pnl_jge, '-' as pnl_grade , 0 as pnl_defect_cnt,");
            sqlBuffDef.append(" '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, count(1) as panel_qty");
            sqlBuffDef.append(" from " + tmpTab.substring(0, 12) + "glass_f defectHis");

            sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
            sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
            sqlBuffDef.append(" and glass_id not like '%*%'");
            sqlBuffDef.append(" and substring(glass_id,1,1) in ('A','C')");
            sqlBuffDef.append(" and ope_id != '7210'");
//            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");


            sqlBuffDef.append(" group by defectHis.ope_id, defectHis.ope_mode, defectHis.cald_date, defectHis.shf_key_fk, ");
            sqlBuffDef.append(" defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner, defectHis.operator_id,");
            sqlBuffDef.append(" defectHis.glass_rej_code, defectHis.cel_repair_grade, defectHis.cel_repair_flg, defectHis.glass_grade,defectHis.source_ope_id");
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");
            sqlBuffDef.append(" union");

            //获取源数据汇总panel_qty
            sqlBuffDef.append(" select ope_id, evt_timestamp, db_timestamp, ope_mode, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, eqpt_id, prod_id, owner, operator_id, glass_rej_code, cel_repair_grade,");
            sqlBuffDef.append(" cel_repair_flg, glass_grade, glass_defect_cnt, source_ope_id, pnl_rej_type, pnl_judgment_grade, pnl_jge,");
            sqlBuffDef.append(" pnl_grade, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5,");
            sqlBuffDef.append(" panel_qty from (");
            sqlBuffDef.append(" select defectHis.ope_id, timestamp'"+ EvtStartTime +"' as evt_timestamp,");
            sqlBuffDef.append(" now() as db_timestamp, defectHis.cald_date, '-' as ope_mode, defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.eqpt_id, defectHis.prod_id, coalesce(defectHis.owner,'-') as owner,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.cald_date, defectHis.shf_key_fk, defectHis.ope_id, defectHis.hour_key_fk, defectHis.prod_id, ");
            sqlBuffDef.append(" defectHis.eqpt_id, defectHis.owner, defectHis.pnl_rej_type, defectHis.pnl_judgment_grade, defectHis.pnl_jge, defectHis.pnl_grade,");
            sqlBuffDef.append(" sum(defectHis.pnl_defect_cnt)) as rowCnt,");
            sqlBuffDef.append(" '-' as operator_id, '-' as glass_rej_code, '-' as cel_repair_grade,");
            sqlBuffDef.append(" '-' as cel_repair_flg, '-' as glass_grade, 0 as glass_defect_cnt, '-' as source_ope_id,");
            sqlBuffDef.append(" coalesce(defectHis.pnl_rej_type,'-') as pnl_rej_type, coalesce(defectHis.pnl_judgment_grade,'-') as pnl_judgment_grade,");
            sqlBuffDef.append(" coalesce(defectHis.pnl_jge,'-') as pnl_jge, coalesce(defectHis.pnl_grade ,'-') as pnl_grade , coalesce(sum(defectHis.pnl_defect_cnt), 0) as pnl_defect_cnt,");
            sqlBuffDef.append(" '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, count(1) as panel_qty");
            sqlBuffDef.append(" from " + tmpTab.substring(0, 12) + "panel_f defectHis");

            sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
            sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
            sqlBuffDef.append(" and panel_id not like '%*%'");
            sqlBuffDef.append(" and substring(panel_id,1,1) in ('A','C')");
//            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");


            sqlBuffDef.append(" group by defectHis.ope_id, defectHis.cald_date, defectHis.shf_key_fk, ");
            sqlBuffDef.append(" defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner, ");
            sqlBuffDef.append(" defectHis.pnl_rej_type, defectHis.pnl_judgment_grade, defectHis.pnl_jge, defectHis.pnl_grade, defectHis.pnl_defect_cnt");
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into eda." + tmpTab);
            sqlInsrDef.append(" (ope_id, evt_timestamp, db_timestamp, ope_mode,");
            sqlInsrDef.append(" cald_date, shf_key, shf_id, hour_key, shop_id, eqpt_id, prod_id, owner, operator_id,");
            sqlInsrDef.append(" glass_rej_code, cel_repair_grade, cel_repair_flg, glass_grade, glass_defect_cnt, source_ope_id,");
            sqlInsrDef.append(" pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_grade, pnl_defect_cnt,");
            sqlInsrDef.append(" ext_1, ext_2, ext_3, ext_4, ext_5, panel_qty)" + sqlBuffDef);
        } else if ("CF".equals(etlConfDModel.getShop())) {
            //获取源数据汇总panel_qty
            sqlBuffDef.append(" select ope_id, evt_timestamp, db_timestamp, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, eqpt_id, prod_id, owner, operator_id, pnl_rej_type, pnl_judgment_grade,");
            sqlBuffDef.append(" pnl_jge, pnl_grade, pnl_defect_cnt, source_pnl_jge, ext_1, ext_2, ext_3, ext_4, ext_5,");
            sqlBuffDef.append(" panel_qty from (");
            sqlBuffDef.append(" select  defectHis.ope_id, timestamp'"+ EvtStartTime +"' as evt_timestamp,");
            sqlBuffDef.append(" now() as db_timestamp, defectHis.cald_date, defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.eqpt_id, defectHis.prod_id, coalesce(defectHis.owner,'-') as owner,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.cald_date, defectHis.shf_key_fk, defectHis.ope_id, defectHis.hour_key_fk, defectHis.prod_id, ");
            sqlBuffDef.append(" defectHis.eqpt_id, defectHis.owner, defectHis.pnl_rej_type, defectHis.pnl_judgment_grade, defectHis.pnl_jge, defectHis.pnl_grade, defectHis.source_pnl_jge,");
            sqlBuffDef.append(" sum(defectHis.pnl_defect_cnt)) as rowCnt,");
            sqlBuffDef.append(" '-' as operator_id, coalesce(defectHis.pnl_rej_type,'-') as pnl_rej_type, coalesce(defectHis.pnl_judgment_grade,'-') as pnl_judgment_grade,");
            sqlBuffDef.append(" coalesce(defectHis.pnl_jge,'-') as pnl_jge, coalesce(defectHis.pnl_grade,'-') as pnl_grade, coalesce(sum(defectHis.pnl_defect_cnt),0) as pnl_defect_cnt, coalesce(defectHis.source_pnl_jge,'-') as source_pnl_jge,");
            sqlBuffDef.append(" '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, count(1) as panel_qty");
            //            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from " + tmpTab.substring(0, 12) + "panel_f defectHis");

            sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
            sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
            sqlBuffDef.append(" and panel_id not like '%*%'");
            sqlBuffDef.append(" and substring(panel_id,1,1) in ('A','C')");
//            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");

            sqlBuffDef.append(" group by defectHis.ope_id, defectHis.cald_date, defectHis.shf_key_fk, ");
            sqlBuffDef.append(" defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner,");
            sqlBuffDef.append(" defectHis.pnl_rej_type, defectHis.pnl_judgment_grade, defectHis.pnl_jge, defectHis.pnl_grade, defectHis.source_pnl_jge");
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into eda." + tmpTab);
            sqlInsrDef.append(" (ope_id, evt_timestamp, db_timestamp,");
            sqlInsrDef.append(" cald_date, shf_key, shf_id, hour_key, shop_id, eqpt_id, prod_id, owner, operator_id,");
            sqlInsrDef.append(" pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_grade, pnl_defect_cnt, source_pnl_jge,");
            sqlInsrDef.append(" ext_1, ext_2, ext_3, ext_4, ext_5, panel_qty)" + sqlBuffDef);
        } else {
            //获取源数据汇总panel_qty
            sqlBuffDef.append(" select ope_id, evt_timestamp, db_timestamp, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, eqpt_id, prod_id, owner, operator_id, pnl_rej_type, pnl_judgment_grade,");
            sqlBuffDef.append(" pnl_jge, pnl_grade, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5,");
            sqlBuffDef.append(" panel_qty from (");
            sqlBuffDef.append(" select defectHis.ope_id, timestamp'"+ EvtStartTime +"' as evt_timestamp,");
            sqlBuffDef.append(" now() as db_timestamp, defectHis.cald_date, defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.eqpt_id, defectHis.prod_id, coalesce(defectHis.owner,'-') as owner,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.cald_date, defectHis.shf_key_fk, defectHis.ope_id, defectHis.hour_key_fk, defectHis.prod_id, ");
            sqlBuffDef.append(" defectHis.eqpt_id, defectHis.owner, defectHis.pnl_rej_type, defectHis.pnl_judgment_grade, defectHis.pnl_jge, defectHis.pnl_grade, ");
            sqlBuffDef.append(" sum(defectHis.pnl_defect_cnt)) as rowCnt,");
            sqlBuffDef.append(" '-' as operator_id, coalesce(defectHis.pnl_rej_type,'-') as pnl_rej_type, coalesce(defectHis.pnl_judgment_grade,'-') as pnl_judgment_grade,");
            sqlBuffDef.append(" coalesce(defectHis.pnl_jge,'-') as pnl_jge, coalesce(defectHis.pnl_grade,'-') as pnl_grade, coalesce(sum(defectHis.pnl_defect_cnt),0) as pnl_defect_cnt,");
            sqlBuffDef.append(" '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, count(1) as panel_qty");
            //            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from " + tmpTab.substring(0, 12) + "panel_f defectHis");

            sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
            sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
            sqlBuffDef.append(" and panel_id not like '%*%'");
            sqlBuffDef.append(" and substring(panel_id,1,1) in ('A','C')");
//            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");

            sqlBuffDef.append(" group by defectHis.ope_id, defectHis.cald_date, defectHis.shf_key_fk, ");
            sqlBuffDef.append(" defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner,");
            sqlBuffDef.append(" defectHis.pnl_rej_type, defectHis.pnl_judgment_grade, defectHis.pnl_jge, defectHis.pnl_grade");
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into eda." + tmpTab);
            sqlInsrDef.append(" (ope_id, evt_timestamp, db_timestamp,");
            sqlInsrDef.append(" cald_date, shf_key, shf_id, hour_key, shop_id, eqpt_id, prod_id, owner, operator_id,");
            sqlInsrDef.append(" pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_grade, pnl_defect_cnt,");
            sqlInsrDef.append(" ext_1, ext_2, ext_3, ext_4, ext_5, panel_qty)" + sqlBuffDef);
        }

        try {
            SQLQuery InsrDef_query = session.createSQLQuery(sqlInsrDef.toString());

            logger.info("Insert data SQL:" + sqlInsrDef.toString());

            Date startTime = new Date();

            InsrDef_query.executeUpdate();

            Date endTime = new Date();
            long durableTime = (endTime.getTime() - startTime.getTime())/1000;

            logger.info("Insert data耗时:" + durableTime +"s");

            okCnt++;
        } catch (ETLException2 e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlInsrDef.toString():  **********    " + sqlInsrDef.toString());

            errCnt++;
            if (!org.apache.commons.lang.StringUtils.isEmpty(errMsg)) {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            } else {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            }
            throw new RuntimeException(e);
        }
    }

    //查寻etl_conf_d表的his job当中的ext_2值
    public List<EtlConfDDefect> getHisJobFlag(EtlConfD etlConfD) {
        List<EtlConfDDefect> etlConfDList = new ArrayList<EtlConfDDefect>();
        QueryRunner qr_mysql = JdbcUtil.getQueryRunner_mysql();

        String jobName = etlConfD.getJobName();
        String hisJobFlagSql = "select ext_2 from ch_qms.etl_conf_d" +
                " where job_group_name = 'RPT_ETL'" +
                " and job_name = '" + jobName + "_His'";

        try {
            logger.info("getHisJobFlag:" + hisJobFlagSql);
            etlConfDList = qr_mysql.query(hisJobFlagSql, new BeanListHandler<EtlConfDDefect>(EtlConfDDefect.class));  //获取etl_conf_d表中his job的ext_2，作为current job的run job的参考
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR get etlHisJob data!" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }

        }

        return etlConfDList;
    }

    //查寻etl_conf_d表的current job当中的ext_2值
    public List<EtlConfDDefect> getCurJob(EtlConfD etlConfD) {
        List<EtlConfDDefect> etlConfDList = new ArrayList<EtlConfDDefect>();
        QueryRunner qr_mysql = JdbcUtil.getQueryRunner_mysql();

        String jobName = etlConfD.getJobName();
        String hisJobFlagSql = "select RUN_END_TIMESTAMP,BUFFER_MINUTE,QUERY_MINUTE,EXT_2 from ch_qms.etl_conf_d" +
                " where job_group_name = 'RPT_ETL'" +
                " and job_name = '" + jobName.substring(0, jobName.length() - 4) + "'";

        try {
            logger.info("getCurJob:" + hisJobFlagSql);
            etlConfDList = qr_mysql.query(hisJobFlagSql, new BeanListHandler<EtlConfDDefect>(EtlConfDDefect.class));  //获取etl_conf_d表中his job的ext_2，作为current job的run job的参考
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR get etlHisJob data!" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }

        }

        return etlConfDList;
    }

    @Transactional(transactionManager = "primaryPlatformTransactionManager")
    public void updateEtlConfD(EtlConfD etlConfD) {
        dao.update(etlConfD);
    }

    @Transactional(transactionManager = "primaryPlatformTransactionManager")
    public void updateEtlConfDModel(EtlConfDModel etlConfDModel) {
        dao.update(etlConfDModel);
    }


    public List<CaldDayShift> getPreReworkShf(EtlConfDModel etlConfDModel, String tableName, List<YearMonth> yearMonthList) {
        List<CaldDayShift> panelList = new ArrayList<>();

        String startTime = "";

        //获取panel的最小release时间，作为起始时间
        String nowTime = LocalDateTime.now().toString();
        int month = Integer.parseInt(yearMonthList.get(0).getMonth(), 16);
        if (month < 10 ) {
            startTime = nowTime.substring(0, 2) + yearMonthList.get(0).getYear() + "-0" + month +"-01 00:00:00";
        } else {
            startTime = nowTime.substring(0, 2) + yearMonthList.get(0).getYear() + "-" + month +"-01 00:00:00";
        }

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        //若最小时间小于2018-03-01 00:00:00，则最小时间赋值为2018-03-01 00:00:00
        try {
            if (df.parse(startTime).getTime() < df.parse("2018-03-01 00:00:00").getTime()) {
                startTime = "2018-03-01 00:00:00";
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }

        MinMaxEvttimeStamp minMaxEvtTime= getMinMaxEvtTime(etlConfDModel.getRunEndTimestamp());

        QueryRunner qr_gp = JdbcUtil.getQueryRunner_gp();
        String sql = "select distinct cald_date, shf_key_fk from " + tableName +
                " where evt_timestamp > '" + startTime + "'" +
                " and evt_timestamp < '" + minMaxEvtTime.getMaxTime() + "'" +
                " and (ope_id, panel_id, jge_cnt) in " +
                " (select distinct ope_id, panel_id, cast(cast(jge_cnt as integer)-1 as varchar) from " + tableName +
                " where evt_timestamp >= '" + minMaxEvtTime.getMinTime() + "'" +
                " and evt_timestamp < '" + minMaxEvtTime.getMaxTime() + "'" +
                " and real_flg = 'Y'" +
                " and panel_id not like '%*%'" +
//                " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                " and jge_cnt ~ '[*0-9]'" +
                " and cast(jge_cnt as integer) > 0" +
                " union" +
                " select distinct ope_id, panel_id, chr(ascii(jge_cnt)-1) from " + tableName +
                " where evt_timestamp >= '" + minMaxEvtTime.getMinTime() + "'" +
                " and evt_timestamp < '" + minMaxEvtTime.getMaxTime() + "'" +
                " and real_flg = 'Y'" +
                " and panel_id not like '%*%'" +
                " and substring(panel_id,1,1) in ('A','C')" +
//                " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                " and jge_cnt ~ '[*A-Z]'" +
                " and jge_cnt not in ('J', 'P')" +
                " union" +
                " select distinct ope_id, panel_id, chr(ascii(jge_cnt)-2) from " + tableName +
                " where evt_timestamp >= '" + minMaxEvtTime.getMinTime() + "'" +
                " and evt_timestamp < '" + minMaxEvtTime.getMaxTime() + "'" +
                " and real_flg = 'Y'" +
                " and panel_id not like '%*%'" +
                " and substring(panel_id,1,1) in ('A','C')" +
//                " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                " and jge_cnt in ('J', 'P'))";

        if ("OC".equals(etlConfDModel.getShop())) {
            sql = sql + " union" +
                    " select distinct cald_date, shf_key_fk from wpp_cdefect_glass_f" +
                    " where evt_timestamp > '" + startTime + "'" +
                    " and evt_timestamp < '" + minMaxEvtTime.getMaxTime() + "'" +
                    " and (ope_id, glass_id, jge_cnt) in " +
                    " (select distinct ope_id, glass_id, cast(cast(jge_cnt as integer)-1 as varchar) from wpp_cdefect_glass_f" +
                    " where evt_timestamp >= '" + minMaxEvtTime.getMinTime() + "'" +
                    " and evt_timestamp < '" + minMaxEvtTime.getMaxTime() + "'" +
                    " and real_flg = 'Y'" +
                    " and glass_id not like '%*%'" +
                    " and substring(glass_id,1,1) in ('A','C')" +
//                " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and jge_cnt ~ '[*0-9]'" +
                    " and cast(jge_cnt as integer) > 0" +
                    " union" +
                    " select distinct ope_id, glass_id, chr(ascii(jge_cnt)-1) from wpp_cdefect_glass_f" +
                    " where evt_timestamp >= '" + minMaxEvtTime.getMinTime() + "'" +
                    " and evt_timestamp < '" + minMaxEvtTime.getMaxTime() + "'" +
                    " and real_flg = 'Y'" +
                    " and glass_id not like '%*%'" +
//                " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and jge_cnt ~ '[*A-Z]'" +
                    " and jge_cnt not in ('J', 'P')" +
                    " union" +
                    " select distinct ope_id, glass_id, chr(ascii(jge_cnt)-2) from wpp_cdefect_glass_f" +
                    " where evt_timestamp >= '" + minMaxEvtTime.getMinTime() + "'" +
                    " and evt_timestamp < '" + minMaxEvtTime.getMaxTime() + "'" +
                    " and real_flg = 'Y'" +
                    " and glass_id not like '%*%'" +
                    " and substring(glass_id,1,1) in ('A','C')" +
//                " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and jge_cnt in ('J', 'P'))";
        }
        try {
            logger.info("查询rework前一次所在天和班别的SQL:" + sql);
            panelList = qr_gp.query(sql, new BeanListHandler<CaldDayShift>(CaldDayShift.class));
        } catch (SQLException e) {
            logger.error("查询rework前一次所在天和班别报错：" + LoggerUtil.stackTraceToString(e));
        }

        return panelList;
    }


    //获取时间段内panel的最小时间，作为delete Panel Summary表中的起始时间
    public List<YearMonth> getYearMonth(EtlConfDModel etlConfDModel, String tmpTab) {
        List<YearMonth> yearMonthList = new ArrayList<YearMonth>();
        QueryRunner qr_gp = JdbcUtil.getQueryRunner_gp();

        MinMaxEvttimeStamp minMaxEvtTime= getMinMaxEvtTime(etlConfDModel.getRunEndTimestamp());

        String sqlBuffDef = "";

        //获取源数据汇总panel_qty
        sqlBuffDef = "select min(substring(glass_id,2,2)) as year, min(substring(glass_id,4,1)) as month" +
                "  from " + tmpTab +
                " where evt_timestamp >= '" + minMaxEvtTime.getMinTime() + "'" +
                " and evt_timestamp < '" + minMaxEvtTime.getMaxTime() + "'" +
//                " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                " and substring(glass_id,1,1) in ('A','C')" +
                " and real_flg = 'Y'" +
                " and glass_id not like '%*%'" +
                " group by glass_id";

        if ("OC".equals(etlConfDModel.getShop())) {
            sqlBuffDef = sqlBuffDef + " union" +
                    " select min(substring(glass_id,2,2)) as year, min(substring(glass_id,4,1)) as month" +
                    " from wpp_cdefect_glass_f" +
                    " where evt_timestamp >= '" + minMaxEvtTime.getMinTime() + "'" +
                    " and evt_timestamp < '" + minMaxEvtTime.getMaxTime() + "'" +
                    " and substring(glass_id,1,1) in ('A','C')" +
                    " and real_flg = 'Y'" +
                    " and glass_id not like '%*%'" +
                    " group by glass_id";
        }

        sqlBuffDef = sqlBuffDef + " order by year, month";

        try {
            logger.info("getYearMonth SQL:" + sqlBuffDef.toString());
            yearMonthList = qr_gp.query(sqlBuffDef, new BeanListHandler<YearMonth>(YearMonth.class));  //获取panel的最小时间
            logger.info("get panel min glass_id time " + "from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!");
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR hisAndExtToTmp GET COLUMN_NAME:" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }
        }

        return yearMonthList;
    }


    public MinMaxEvttimeStamp getMinMaxEvtTime(Timestamp endTimeStamp) {
        MinMaxEvttimeStamp minMaxEvtTime = new MinMaxEvttimeStamp();

        String minTime = "";
        String maxTime = "";

        long firstTime = 0;
        long secondTime = 0;
        long thirdTime = 0;
        long fourth = 0;

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String day = df.format(endTimeStamp).substring(0, 10);
        long endTime = endTimeStamp.getTime();

        try {
            firstTime = df.parse(day + " 08:30:00").getTime();
            secondTime = df.parse(day + " 20:30:00").getTime();
            thirdTime = df.parse(day + " 08:30:00").getTime() + 1000*60*60*24;
            fourth = df.parse(day + " 20:30:00").getTime() - 1000*60*60*24;
        } catch (ParseException e) {
            e.printStackTrace();
        }

        if (endTime > firstTime && endTime <= secondTime) {
            minTime = df.format(firstTime);
            maxTime = df.format(secondTime);
        } else if (endTime > secondTime && endTime <= thirdTime) {
            minTime = df.format(secondTime);
            maxTime = df.format(thirdTime);
        } else if (endTime > fourth && endTime <= firstTime) {
            minTime = df.format(fourth);
            maxTime = df.format(firstTime);
        }

        //若最小时间小于2018-03-01 00:00:00，则最小时间赋值为2018-03-01 00:00:00
        try {
            if (df.parse(minTime).getTime() < df.parse("2018-03-01 00:00:00").getTime()) {
                minTime = "2018-03-01 00:00:00";
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }

        minMaxEvtTime.setMinTime(minTime);
        minMaxEvtTime.setMaxTime(maxTime);

        return minMaxEvtTime;
    }

    public ShiftStartEndTime getShiftTime(String caldDate, String shfKeyFk) {
        ShiftStartEndTime shiftStartEndTime = new ShiftStartEndTime();

        String startTime = "";
        String endTime = "";
        int shfKey = Integer.valueOf(shfKeyFk);

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        if (shfKey % 2 == 0) {
            startTime = caldDate + " 20:30:00";
            try {
                endTime = df.format(df.parse(caldDate + " 08:30:00").getTime() + 1000*60*60*24);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        } else {
            startTime = caldDate + " 08:30:00";
            endTime = caldDate + " 20:30:00";
        }

        shiftStartEndTime.setStartTime(startTime);
        shiftStartEndTime.setEndTime(endTime);

        return shiftStartEndTime;
    }
}
